﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDAOrderMaster
    {
        public static Boolean Delete(OrderMaster orderMaster, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = "";
            string _cmdText = " DELETE orderMaster " +
                              " WHERE ID = " + orderMaster.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Update(OrderMaster orderMaster, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = "";
            string _cmdText = " UPDATE ordermaster SET " +
                              "  ContactID = " + ((string.IsNullOrEmpty(orderMaster.Contact.ID.ToString()) || (orderMaster.Contact.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.Contact.ID.ToString()) +
                              " ,CompanyID = " + ((string.IsNullOrEmpty(orderMaster.Company.ID.ToString()) || (orderMaster.Company.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.Company.ID.ToString()) +
                              " ,InsertDateTime = " + (orderMaster.InsertDateTime == null ? "NULL" : "'" + Convert.ToDateTime(orderMaster.InsertDateTime).ToString() + "'") +
                              " ,Description = " + (orderMaster.Description == null ? "NULL" : "'" + orderMaster.Description.ToString() + "'") +
                              " ,UserID_Delivery = " + ((string.IsNullOrEmpty(orderMaster.User.ID.ToString()) || (orderMaster.User.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.User.ID.ToString()) +
                              " ,Price = " + ((string.IsNullOrEmpty(orderMaster.Price.ToString()) || (orderMaster.Price == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.Price.ToString().Replace(',', '.')) +
                              " ,Price_Addition = " + ((string.IsNullOrEmpty(orderMaster.PriceAddition.ToString()) || (orderMaster.PriceAddition == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.PriceAddition.ToString().Replace(',', '.')) +
                              " ,Price_Discount = " + ((string.IsNullOrEmpty(orderMaster.PriceDiscount.ToString()) || (orderMaster.PriceDiscount == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.PriceDiscount.ToString().Replace(',', '.')) +
                              " ,Price_Tax = " + ((string.IsNullOrEmpty(orderMaster.PriceTax.ToString()) || (orderMaster.PriceTax == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.PriceTax.ToString().Replace(',', '.')) +
                              " ,OrderTypeID = " + ((string.IsNullOrEmpty(orderMaster.OrderType.ID.ToString()) || (orderMaster.OrderType.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.OrderType.ID.ToString()) +
                              " WHERE ID = " + orderMaster.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Insert(OrderMaster orderMaster, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " INSERT INTO ordermaster (ContactID, CompanyID, Description, UserID_Delivery, Price, Price_Addition, Price_Discount, Price_Tax, OrderTypeID) " +
                              " VALUES(" + ((string.IsNullOrEmpty(orderMaster.Contact.ID.ToString()) || (orderMaster.Contact.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.Contact.ID.ToString()) +
                              " ," + ((string.IsNullOrEmpty(orderMaster.Company.ID.ToString()) || (orderMaster.Company.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.Company.ID.ToString()) +
                              " ," + (orderMaster.Description == null ? "NULL" : "'" + orderMaster.Description.ToString() + "'") +
                              " ," + ((string.IsNullOrEmpty(orderMaster.User.ID.ToString()) || (orderMaster.User.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.User.ID.ToString()) +
                              " ," + ((string.IsNullOrEmpty(orderMaster.Price.ToString()) || (orderMaster.Price == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.Price.ToString().Replace(',', '.')) +
                              " ," + ((string.IsNullOrEmpty(orderMaster.PriceAddition.ToString()) || (orderMaster.PriceAddition == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.PriceAddition.ToString().Replace(',', '.')) +
                              " ," + ((string.IsNullOrEmpty(orderMaster.PriceDiscount.ToString()) || (orderMaster.PriceDiscount == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.PriceDiscount.ToString().Replace(',', '.')) +
                              " ," + ((string.IsNullOrEmpty(orderMaster.PriceTax.ToString()) || (orderMaster.PriceTax == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.PriceTax.ToString().Replace(',', '.')) +
                              " ," + ((string.IsNullOrEmpty(orderMaster.OrderType.ID.ToString()) || (orderMaster.OrderType.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : orderMaster.OrderType.ID.ToString()) +
                              ")";

            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                _cmdText = " SELECT last_insert_id(); ";
                cmd = new MySqlCommand(_cmdText, con);
                int _id = Convert.ToInt32(cmd.ExecuteScalar());
                orderMaster.ID = _id;

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static List<OrderMaster> Select(OrderMaster orderMaster)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string _cmdText =
            " SELECT o.* " +
            " FROM ordermaster o " +
            " WHERE ((o.ID = " + orderMaster.ID.ToString() + ") OR (" + orderMaster.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "ORDER BY o.ID";

            SqlCommand cmd = new SqlCommand(_cmdText, con);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<OrderMaster> _orderMasterList = new List<OrderMaster>();

            foreach (DataRow row in tbl.Rows)
            {
                OrderMaster _orderMaster = new OrderMaster();
                _orderMaster.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));

                _orderMaster.Contact = new Contact();
                _orderMaster.Contact.ID = (row["ContactID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ContactID"]));

                _orderMaster.Company = new Company();
                _orderMaster.Company.ID = (row["CompanyID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CompanyID"]));
                
                _orderMaster.InsertDateTime = Convert.ToDateTime(row["InsertDateTime"]);
                _orderMaster.Description = (row["Description"].ToString() == "" ? null : row["Description"].ToString());

                _orderMaster.User = new User();
                _orderMaster.User.ID = (row["UserID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["UserID"]));

                _orderMaster.Price = (row["Price"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["Price"]));
                _orderMaster.PriceAddition = (row["Price_Addition"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["Price_Addition"]));
                _orderMaster.PriceDiscount = (row["Price_Discount"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["Price_Discount"]));
                _orderMaster.PriceTax = (row["Price_Tax"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToDecimal(row["Price_Tax"]));

                _orderMaster.OrderType = new OrderType();
                _orderMaster.OrderType.ID = (row["OrderTypeID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["OrderTypeID"]));
                
                _orderMasterList.Add(_orderMaster);
            }

            con.Close();
            return _orderMasterList;
        }
    }
}